package cn.edu.nju.ws.sview.views;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.edu.nju.ws.sview.database.DBConnectionFactory;
import cn.edu.nju.ws.sview.database.DBOperations;
import cn.edu.nju.ws.sview.util.PropertyList;

/**
 * 对实体特定语言的描述
 * @author Saisai Gong
 *
 */
public class Comment {
	
	private long time = 0;
	private String groupName = null;
	private String language = null;
	private String text = null;
	private int member_type;
	private int member_id;
	private int id;
	
	public Comment(String groupName, long time,String lang,String text,int member_type,int member_id) {
		this.groupName = groupName;
		this.time = time;
		this.language = lang;
		this.text = text;
		this.member_type = member_type;
		this.member_id = member_id;
	}
	
	public int getID(){
		return this.id;
	}
	
	public void setID(int id){
		this.id = id;
	}
	
	/**
	 * get the origin optimal comment of the origin uri of the specific lang
	 * if the lang is not specified, belong to en default
	 * @param uri
	 * @param lang
	 * @return empty if no statement in dereference doc is found
	 */
	public static String getOriginComment(String uri,String lang) throws Throwable{
		String comment = "";
		if (uri == null) {
			return comment;
		}

		ArrayList<String> properties = PropertyList.getCommentProperties();
		ArrayList<String> array = new ArrayList<String>();
		for(String str:properties){
			int p = DBOperations.getURIID(str,true) ;
			array.add("u"+p) ;
		}
		
		int s = DBOperations.getURIID(uri,false);
		int doc_id = DBOperations.getDereferenceDocID(uri);
		Connection conn = DBConnectionFactory.getConnection();
		PreparedStatement stmt = null;
		if(lang==null){
			stmt = conn
			 .prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and o like 'l%'");
            stmt.setString(1, "u"+s) ;
			stmt.setInt(2, doc_id);
		}else if(lang.equals("en")){
			stmt = conn
			.prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and ((lang like 'en%') or (lang='' )) and o like 'l%'");
            stmt.setString(1, "u"+s) ;
			stmt.setInt(2, doc_id);
		}else{
			stmt = conn
			.prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and lang=? and o like 'l%'");
		    stmt.setString(1, "u"+s) ;
			stmt.setInt(2, doc_id);
			stmt.setString(4, lang) ;
		}
		
		for(String property:array){
			stmt.setString(3, property) ;
			ResultSet rs = stmt.executeQuery();
			if(rs.next()){
				comment = rs.getString("o").substring(1);
				break;
			}
			rs.close();
		}
		stmt.close();
		conn.close();
		
		return comment;		
	}
	
	 public static Comment getCommentByMemberLangGroup(
			int memberType ,int memberId, String language, String groupName) {
		Comment result = null;
		
		StringBuffer sqlBuffer = new StringBuffer(String.format(
				"select id,time, group_id,lang,text " +
				"from comment " +
				"where member_type = %d and member_id = %d ", 
				memberType, memberId));
		if ( groupName != null ) {
			int groupID = Group.getGroupIdByName(groupName);
			sqlBuffer.append(String.format("and group_id = %d ", groupID));
		}
		if ( language != null ) {
			sqlBuffer.append(String.format("and lang = '%s' ", language));
		}
		
		try {
			Connection conn = DBConnectionFactory.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlBuffer.toString());
			while ( rs.next() ) {
				long time = rs.getLong("time");
				String mgroup = Group.getGroupNameById(rs.getInt("group_id"));
				String mlanguage = rs.getString("lang");
				String text = rs.getString("text");
				int id = rs.getInt("id");
				result = new Comment(mgroup, time,mlanguage,text,memberType,memberId);
				result.setID(id) ;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
    public int getMemberType(){
    	return this.member_type;
    }
   
    public int getMemberID(){
    	return this.member_id;
    }
	
	public String getLang() {
		return this.language;
	}
	
	public String getText() {
		return this.text;
	}
	
	public static boolean existCommentOfLang(String groupName, String lang, int member_type,int member_id){
		boolean result = false;
		int groupID = Group.getGroupIdByName(groupName);
		if ( groupID < 0 ) return result;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			
			PreparedStatement stmt = conn.prepareStatement(
					"select null from  comment where group_id=? and  lang=? and  member_type=? and member_id=?");
			stmt.setInt(1, groupID);
			stmt.setString(2, lang);
			stmt.setInt(3, member_type);
			stmt.setInt(4, member_id);
			ResultSet rsResultSet = stmt.executeQuery();
			if(rsResultSet.next()){
				result = true;
			}
			rsResultSet.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	public void serialize() {
		int groupID = Group.getGroupIdByName(groupName);
		if ( groupID < 0 ) return;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			
			PreparedStatement stmt = conn.prepareStatement(
					"insert into comment(time, group_id, lang,text, member_type, member_id) " +
					"values(?,?,?,?,?,?) on duplicate key update text=values(text), time=values(time)");		
			stmt.setLong(1, this.time);
			stmt.setInt(2, groupID);
			stmt.setString(3, this.language);
			stmt.setString(4, this.text);
			stmt.setInt(5, this.member_type);
			stmt.setInt(6, this.member_id);
			stmt.executeUpdate();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public String getGroupName() {
		return groupName;
	}
	
	public long getTime() {
		return time;
	}
	
	public void setLang(String lang) {
		this.language = lang;
	}
	
	public void setText(String text) {
		this.text = text;
	}
	
	/**
	 * 删除与一个comment有关的信息
	 * @param commentID
	 */
	public static void delete(int commentID){
		Connection connection = DBConnectionFactory.getConnection();
		
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(String.format("delete from comment where id=%d",commentID));
			stmt.executeUpdate(String.format("delete from comment_prefer where comment_id=%d",commentID));
			stmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public int hashCode() {
		return new Long(time).hashCode() * 31 + groupName.hashCode();
	}
	
	@Override
	public boolean equals(Object obj) {
		return time == ((Comment) obj).time && 
				groupName.equals(((Comment) obj).groupName);
	}
}
